package db;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.TimeZone;

import model.*;

public class DB {
    private static final Object UTC= TimeZone.getTimeZone("UTC");
    Connection ct;
    PreparedStatement pstmt;
    String url;
    String user;
    String password;
    public DB(){
        url= "jdbc:mysql://localhost:3306/iosystem?characterEncoding=UTF-8"+"&serverTimezone=UTC"+"&useSSL=false";
        user ="root";
        password ="QiRui2002";
        try{
            Class.forName("com.mysql.jdbc.Driver");
            ct=DriverManager.getConnection(url,user,password);
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    public User login(String username,String password){
        try{
            pstmt=ct.prepareStatement("select * from users where User_name=? and passwords=?");
            pstmt.setString(1,username);
            pstmt.setString(2, password);
            ResultSet rs=pstmt.executeQuery();
            User user=new User();
            while(rs.next()){
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setroles(rs.getString(4));
                user.setPermission(rs.getByte(5));
                return user;
            }
            return null;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public ArrayList findgoodInfo(){
        try{
            pstmt=ct.prepareStatement("select * from good");
            ArrayList gl=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                good gd=new good();
                gd.setGoods_id(rs.getInt(1));
                gd.setGood_name(rs.getString(2));
                gd.setpurchase_price(rs.getDouble(5));
                gd.setselling_price(rs.getDouble(6));
                if(rs.getString(7) == null||rs.getString(7).equals("")){gd.setremarks("无");}
                else {gd.setremarks(rs.getString(7));}
                gl.add(gd);
            }
            return gl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public boolean addgood(good gd){
        try{
            pstmt=ct.prepareStatement("insert into good(Good_name,Good_quantity,lowerLimit,purchase_price,selling_price,remarks) values (?, ?, ?, ?, ?,?);");
            pstmt.setString(1, gd.getGood_name());
            pstmt.setInt(2, 0);
            pstmt.setInt(3, 0);
            pstmt.setDouble(4, gd.getpurchase_price());
            pstmt.setDouble(5, gd.getselling_price());
            pstmt.setString(6, gd.getremarks());
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean deletegood(int goodid){
        try{
            pstmt=ct.prepareStatement("delete from Good where Goods_id=?;");
            pstmt.setInt(1, goodid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean editgood(good gd,int goodid){
        try{
            pstmt=ct.prepareStatement("update Good set Good_name=?,purchase_price=?,selling_price=?,remarks=? where Goods_id=?;");
            pstmt.setString(1, gd.getGood_name());
            pstmt.setDouble(2, gd.getpurchase_price());
            pstmt.setDouble(3, gd.getselling_price());
            pstmt.setString(4, gd.getremarks());
            pstmt.setInt(5, goodid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public ArrayList findsupplyInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Suppliers;");
            ArrayList sl=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                suppliers sp=new suppliers();
                sp.setSupplier_id(rs.getInt(1));
                sp.setSupplier_name(rs.getString(2));
                sp.setSupplier_phoneNumber(rs.getString(3));
                if(rs.getString(4) == null||rs.getString(4).equals("")){sp.setremarks("无");}
                else {sp.setremarks(rs.getString(4));}
                sl.add(sp);
            }
            return sl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public boolean addsupply(suppliers sp){
        try{
            pstmt=ct.prepareStatement("insert into Suppliers(Supplier_name,Supplier_phoneNumer,remarks) values (?, ?,?);");
            pstmt.setString(1, sp.getSupplier_name());
            pstmt.setString(2,sp.getSupplier_phoneNumber());
            pstmt.setString(3, sp.getremarks());
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean deletesupply(int supplyid){
        try{
            pstmt=ct.prepareStatement("delete from Suppliers where Supplier_id=?;");
            pstmt.setInt(1, supplyid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean editsupply(suppliers sp,int supplyid){
        try{
            pstmt=ct.prepareStatement("update Suppliers set Supplier_name=?,Supplier_phoneNumer=?,remarks=? where Supplier_id=?;");
            pstmt.setString(1, sp.getSupplier_name());
            pstmt.setString(2, sp.getSupplier_phoneNumber());
            pstmt.setString(3, sp.getremarks());
            pstmt.setInt(4, supplyid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public ArrayList findcustomerInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Customer;");
            ArrayList cl=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                customer cu=new customer();
                cu.setCustomer_id(rs.getInt(1));
                cu.setCustomer_name(rs.getString(2));
                cu.setCustomer_phoneNumber(rs.getString(3));
                if(rs.getString(4) == null||rs.getString(4).equals("")){cu.setremarks("无");}
                else {cu.setremarks(rs.getString(4));}
                cl.add(cu);
            }
            return cl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public boolean addcustomer(customer cu){
        try{
            pstmt=ct.prepareStatement("insert into Customer(Customer_name,Customer_phoneNumer,remarks) values (?, ?,?);");
            pstmt.setString(1, cu.getCustomer_name());
            pstmt.setString(2,cu.getCustomer_phoneNumber());
            pstmt.setString(3, cu.getremarks());

            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean deletecustomer(int customerid){
        try{
            pstmt=ct.prepareStatement("delete from Customer where Customer_id=?;");
            pstmt.setInt(1, customerid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean editcustomer(customer cm,int customerid){
        try{
            pstmt=ct.prepareStatement("update Customer set Customer_name=?,Customer_phoneNumer=?,remarks=? where Customer_id=?;");
            pstmt.setString(1, cm.getCustomer_name());
            pstmt.setString(2, cm.getCustomer_phoneNumber());
            pstmt.setString(3, cm.getremarks());
            pstmt.setInt(4, customerid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean addplist(int supplyid,int state,String remark,int userid){
        try{
            pstmt=ct.prepareStatement("insert into Purchase_List(Purchase_List_amount,state,Supplier_id,User_id,remarks) values (0,?,?,?,?);");
            pstmt.setInt(1, state);
            pstmt.setInt(2,supplyid);
            pstmt.setInt(3,userid);
            pstmt.setString(4, remark);

            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean updatepurchaselist(int listid){
        try{
            pstmt=ct.prepareStatement("update Purchase_List set Purchase_List_amount= (select round(sum(purchase_price*Purchase_quantity),2) from Purchase_List_Goods,Good where Purchase_List_id=? and Purchase_List_Goods.Goods_id=Good.Goods_id) where Purchase_List_id=?;");
            pstmt.setInt(1, listid);
            pstmt.setInt(2, listid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateplist(int listid){
        try{
            if(updatepurchaselist(listid))return true;
            else{
                pstmt=ct.prepareStatement("update Purchase_List set Purchase_List_amount=0 where Purchase_List_id=?;");
                pstmt.setInt(1, listid);
                pstmt.executeUpdate();
                return true;
            }

        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public ArrayList findplistInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Purchase_List;");
            ArrayList pll=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                purchase_list pl=new purchase_list();
                pl.setPurchase_List_id(rs.getInt(1));
                pl.setPurchase_List_date(rs.getDate(2));
                pl.setPurchase_List_amount(rs.getDouble(3));
                pl.setstate(rs.getByte(4));
                pl.setSupplier_id(rs.getInt(5));
                pl.setUser_id(rs.getInt(6));
                if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setremarks("无");}
                else {pl.setremarks(rs.getString(7));}
                pll.add(pl);
            }
            return pll;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public ArrayList findunplistInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Purchase_List where state=0;");
            ArrayList pll=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                purchase_list pl=new purchase_list();
                pl.setPurchase_List_id(rs.getInt(1));
                pl.setPurchase_List_date(rs.getDate(2));
                pl.setPurchase_List_amount(rs.getDouble(3));
                pl.setstate(rs.getByte(4));
                pl.setSupplier_id(rs.getInt(5));
                pl.setUser_id(rs.getInt(6));
                if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setremarks("无");}
                else {pl.setremarks(rs.getString(7));}
                pll.add(pl);
            }
            return pll;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public String findsupplyname(int id){
        try{
            pstmt=ct.prepareStatement("select Supplier_name from Suppliers where Supplier_id=?;");
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getString(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public String findgoodname(int id){
        try{
            pstmt=ct.prepareStatement("select Good_name from Good where Goods_id=?;");
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getString(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public purchase_list findplist(int id){
        try{
            pstmt=ct.prepareStatement("select * from Purchase_List where Purchase_List_id=?");
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            purchase_list pl = new purchase_list();
            rs.next();
            pl.setPurchase_List_id(rs.getInt(1));
            pl.setPurchase_List_date(rs.getDate(2));
            pl.setPurchase_List_amount(rs.getDouble(3));
            pl.setstate(rs.getByte(4));
            pl.setSupplier_id(rs.getInt(5));
            pl.setUser_id(rs.getInt(6));
            if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setremarks("无");}
            else {pl.setremarks(rs.getString(7));}
            return pl;

        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public purchase_list getnewplist() {
        try{
            pstmt=ct.prepareStatement("select * from Purchase_List where Purchase_List_date<=current_timestamp() order by Purchase_List_date desc limit 1;");
            ResultSet rs=pstmt.executeQuery();
            purchase_list pl = new purchase_list();
            rs.next();
            pl.setPurchase_List_id(rs.getInt(1));
            pl.setPurchase_List_date(rs.getDate(2));
            pl.setPurchase_List_amount(rs.getDouble(3));
            pl.setstate(rs.getByte(4));
            pl.setSupplier_id(rs.getInt(5));
            pl.setUser_id(rs.getInt(6));
            if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setremarks("无");}
            else {pl.setremarks(rs.getString(7));}
            return pl;

        }catch(Exception e){
            e.printStackTrace();
            return null;
        }

    }

    public ArrayList findpgoodlistInfo(int id){
        try{
            pstmt=ct.prepareStatement("select * from Purchase_List_Goods where Purchase_List_id=?;");
            ArrayList pgl=new ArrayList();
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                purchase_list_goods pg=new purchase_list_goods();
                pg.setPurchase_List_id(rs.getInt(1));
                pg.setGoods_id(rs.getInt(2));
                pg.setPurchase_quantity(rs.getInt(3));
                pgl.add(pg);
            }
            return pgl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }



    public boolean changestock(int goodid,int num){
        try{
            pstmt=ct.prepareStatement("update Good set Good_quantity=Good_quantity+? where Goods_id=?;");
            pstmt.setInt(1, num);
            pstmt.setInt(2, goodid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public int checkpurchasestock(int listid ,int goodid){
        try{
            pstmt=ct.prepareStatement("select Good_quantity-Purchase_quantity from Good,Purchase_list_goods where Purchase_List_id=? and Purchase_List_goods.Goods_id=? and Purchase_List_goods.Goods_id=Good.Goods_id;");
            pstmt.setInt(1, listid);
            pstmt.setInt(2, goodid);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return 0;
        }
    }

    public boolean editplistgood(purchase_list_goods gd){
        try{
            pstmt=ct.prepareStatement("update Purchase_List_Goods set Purchase_quantity=? where Purchase_List_id=? and Goods_id=?;");
            pstmt.setInt(1, gd.getPurchase_quantity());
            pstmt.setInt(2, gd.getPurchase_List_id());
            pstmt.setInt(3, gd.getGoods_id());
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean editgoodstock(int goodid,int stock){
        try{
            pstmt=ct.prepareStatement("update Good set Good_quantity=? where Goods_id=?;");
            pstmt.setInt(1, stock);
            pstmt.setInt(2, goodid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean addplistgood(purchase_list_goods plg){
        try{
            pstmt=ct.prepareStatement("insert into Purchase_List_Goods values (?,?,?);");
            pstmt.setInt(1, plg.getPurchase_List_id());
            pstmt.setInt(2,plg.getGoods_id());
            pstmt.setInt(3, plg.getPurchase_quantity());

            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteplistgood(int listid,int goodid){
        try{
            pstmt=ct.prepareStatement("delete from Purchase_List_Goods where Purchase_List_id=? and Goods_id=?;");
            pstmt.setInt(1, listid);
            pstmt.setInt(2, goodid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteplist(int listid){
        try{
            pstmt=ct.prepareStatement("delete from Purchase_List where Purchase_List_id=?;");
            pstmt.setInt(1, listid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean pliststate(int listid){
        try{
            pstmt=ct.prepareStatement("update Purchase_List set state=1 where Purchase_List_id=?;");
            pstmt.setInt(1, listid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean addslist(int customerid,int state,String remark,int userid){
        try{
            pstmt=ct.prepareStatement("insert into Sale_List(Sale_List_amount,state,Customer_id,User_id,remarks) values (0,?,?,?,?);");
            pstmt.setInt(1, state);
            pstmt.setInt(2,customerid);
            pstmt.setInt(3,userid);
            pstmt.setString(4, remark);

            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
//
    public boolean updatesalelist(int listid){
        try{
            pstmt=ct.prepareStatement("update Sale_List set Sale_List_amount= (select round(sum(selling_price*Sale_quantity),2) from Sale_List_good,Good where Sale_List_id=? and Sale_List_good.Goods_id=Good.Goods_id)where Sale_List_id=?;");
            pstmt.setInt(1, listid);
            pstmt.setInt(2, listid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateslist(int listid){
        try{
            if(updatesalelist(listid))return true;
            else{
                pstmt=ct.prepareStatement("update Sale_List set Sale_List_amount=0 where Sale_List_id=?;");
                pstmt.setInt(1, listid);
                pstmt.executeUpdate();
                return true;
            }

        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
//
    public ArrayList findslistInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Sale_List");
            ArrayList pll=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                sale_list pl=new sale_list();
                pl.setSale_List_id(rs.getInt(1));
                pl.setSale_List_date(rs.getDate(2));
                pl.setSale_List_amount(rs.getDouble(3));
                pl.setstate(rs.getByte(4));
                pl.setCustomer_id(rs.getInt(5));
                pl.setUser_id(rs.getInt(6));
                if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setRemarks("无");}
                else {pl.setRemarks(rs.getString(7));}
                pll.add(pl);
            }
            return pll;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }
//
    public ArrayList findunslistInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Sale_List where state=0;");
            ArrayList pll=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                sale_list pl=new sale_list();
                pl.setSale_List_id(rs.getInt(1));
                pl.setSale_List_date(rs.getDate(2));
                pl.setSale_List_amount(rs.getDouble(3));
                pl.setstate(rs.getByte(4));
                pl.setCustomer_id(rs.getInt(5));
                pl.setUser_id(rs.getInt(6));
                if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setRemarks("无");}
                else {pl.setRemarks(rs.getString(7));}
                pll.add(pl);
            }
            return pll;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }
//
    public String findcustomername(int id){
        try{
            pstmt=ct.prepareStatement("select Customer_name from Customer where Customer_id=?;");
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getString(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }
//

//
    public sale_list findslist(int id){
        try{
            pstmt=ct.prepareStatement("select * from Sale_List where Sale_List_id=?;");
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            sale_list pl = new sale_list();
            rs.next();
            pl.setSale_List_id(rs.getInt(1));
            pl.setSale_List_date(rs.getDate(2));
            pl.setSale_List_amount(rs.getDouble(3));
            pl.setstate(rs.getByte(4));
            pl.setCustomer_id(rs.getInt(5));
            pl.setUser_id(rs.getInt(6));
            if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setRemarks("无");}
            else {pl.setRemarks(rs.getString(7));}
            return pl;

        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }
//
    public sale_list getnewslist() {
        try{
            pstmt=ct.prepareStatement("select * from Sale_List where Sale_List_date<=current_timestamp() order by Sale_List_date desc limit 1;");
            ResultSet rs=pstmt.executeQuery();
            sale_list pl = new sale_list();
            rs.next();
            pl.setSale_List_id(rs.getInt(1));
            pl.setSale_List_date(rs.getDate(2));
            pl.setSale_List_amount(rs.getDouble(3));
            pl.setstate(rs.getByte(4));
            pl.setCustomer_id(rs.getInt(5));
            pl.setUser_id(rs.getInt(6));
            if(rs.getString(7) == null||rs.getString(7).equals("")){pl.setRemarks("无");}
            else {pl.setRemarks(rs.getString(7));}
            return pl;

        }catch(Exception e){
            e.printStackTrace();
            return null;
        }

    }
//
    public ArrayList findsgoodlistInfo(int id){
        try{
            pstmt=ct.prepareStatement("select * from Sale_List_good where Sale_List_id=?;");
            ArrayList pgl=new ArrayList();
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                sale_list_good pg=new sale_list_good();
                pg.setSale_List_id(rs.getInt(1));
                pg.setGoods_id(rs.getInt(2));
                pg.setSale_quantity(rs.getInt(3));
                pgl.add(pg);
            }
            return pgl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }
//
//
    public int checkstock(int goodid){
        try{
            pstmt=ct.prepareStatement("select Good_quantity from Good where Goods_id=?;");
            pstmt.setInt(1, goodid);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return 0;
        }
    }

    public int checksalestock(int listid,int goodid){
        try{
            pstmt=ct.prepareStatement("select Good_quantity+Sale_quantity from Good,Sale_List_good where Sale_List_id=? and Sale_List_good.Goods_id=? and Sale_List_good.Goods_id=Good.Goods_id;");
            pstmt.setInt(1, listid);
            pstmt.setInt(2, goodid);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return 0;
        }
    }
//
    public boolean editslistgood(sale_list_good gd){
        try{
            pstmt=ct.prepareStatement("update Sale_List_good set Sale_quantity=? where Sale_List_id=? and Goods_id=?;");
            pstmt.setInt(1, gd.getSale_quantity());
            pstmt.setInt(2, gd.getSale_List_id());
            pstmt.setInt(3, gd.getGoods_id());
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
//
    public boolean addslistgood(sale_list_good plg){
        try{
            pstmt=ct.prepareStatement("insert into Sale_List_good values (?,?,?);");
            pstmt.setInt(1, plg.getSale_List_id());
            pstmt.setInt(2,plg.getGoods_id());
            pstmt.setInt(3, plg.getSale_quantity());

            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
//
    public boolean deleteslistgood(int listid,int goodid){
        try{
            pstmt=ct.prepareStatement("delete from Sale_List_good where Sale_List_id=? and Goods_id=?;");
            pstmt.setInt(1, listid);
            pstmt.setInt(2, goodid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
//
    public boolean deleteslist(int listid){
        try{
            pstmt=ct.prepareStatement("delete from Sale_List where Sale_List_id=?;");
            pstmt.setInt(1, listid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
//
    public boolean sliststate(int listid){
        try{
            pstmt=ct.prepareStatement("update Sale_List set state=1 where Sale_List_id=?;");
            pstmt.setInt(1, listid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public ArrayList findgoodstockInfo(){
        try{
            pstmt=ct.prepareStatement("select * from good");
            ArrayList gl=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                good gd=new good();
                gd.setGoods_id(rs.getInt(1));
                gd.setGood_name(rs.getString(2));
                gd.setGood_quantity(rs.getInt(3));
                gd.setlowerLimit(rs.getInt(4));
                gl.add(gd);
            }
            return gl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public ArrayList findgoodstockwarningInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Good where Good_quantity<=lowerLimit;");
            ArrayList gl=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                good gd=new good();
                gd.setGoods_id(rs.getInt(1));
                gd.setGood_name(rs.getString(2));
                gd.setGood_quantity(rs.getInt(3));
                gd.setlowerLimit(rs.getInt(4));
                gl.add(gd);
            }
            return gl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public boolean editgoodstockwarning(int goodid,int stock){
        try{
            pstmt=ct.prepareStatement("update Good set lowerLimit=? where Goods_id=?;");
            pstmt.setInt(1, stock);
            pstmt.setInt(2, goodid);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public ArrayList finduserInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Users;");
            ArrayList ul=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                User us=new User();
                us.setId(rs.getInt(1));
                us.setUsername(rs.getString(2));
                us.setPassword(rs.getString(3));
                us.setroles(rs.getString(4));
                us.setPermission(rs.getByte(5));
                ul.add(us);
            }
            return ul;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public String findpermisionname(int id){
        try{
            pstmt=ct.prepareStatement("select Permission_name from Permission where Permission_id=?;");
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getString(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public ArrayList findpermissionInfo(){
        try{
            pstmt=ct.prepareStatement("select * from Permission;");
            ArrayList pl=new ArrayList();

            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                permission pe=new permission();
                pe.setPermission_id(rs.getByte(1));
                pe.setPermission_name(rs.getString(2));
                pl.add(pe);
            }
            return pl;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public permission findpermission(Byte id){
        try{
            pstmt=ct.prepareStatement("select * from Permission where Permission_id=?;");
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            permission pe=new permission();
            pe.setPermission_id(rs.getByte(1));
            pe.setPermission_name(rs.getString(2));
            pe.setpurchase(rs.getByte(3));
            pe.setselling(rs.getByte(4));
            pe.setquantity(rs.getByte(5));
            pe.setalters(rs.getByte(6));
            pe.setmanage(rs.getByte(7));
            return pe;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public boolean adduser(User us){
        try{
            pstmt=ct.prepareStatement("insert into Users(User_name,passwords,roles,Permission_id) values (?, ?,?,?);");
            pstmt.setString(1, us.getUsername());
            pstmt.setString(2, us.getPassword());
            pstmt.setString(3, us.getroles());
            pstmt.setByte(4,us.getpermision());
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean edituser(int id,String roles,Byte permission){
        try{
            pstmt=ct.prepareStatement("update Users set roles=?,Permission_id=? where User_id=?;");
            pstmt.setString(1, roles);
            pstmt.setByte(2,permission);
            pstmt.setInt(3,id);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean isop(int id){
        try{
            pstmt=ct.prepareStatement("select Permission_id from Users where User_id=?;");
            pstmt.setInt(1,id);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            if(rs.getByte(1)==1)return true;
            else return false;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteuser(int id){
        try{
            pstmt=ct.prepareStatement("delete from Users where User_id=?;");
            pstmt.setInt(1,id);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
    public boolean updatepwd(String pwd,int id){
        try{
            pstmt=ct.prepareStatement("update Users set passwords=? where User_id=?;");
            pstmt.setString(1,pwd);
            pstmt.setInt(2,id);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }



    public User checkUser(String username,String password){
        try{
            pstmt=ct.prepareStatement("select * from userTable where username=? and password=?");
            pstmt.setString(1,username);
            pstmt.setString(2, password);
            ResultSet rs=pstmt.executeQuery();
            User user=new User();
            while(rs.next()){
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                return user;
            }
            return null;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public Integer expand(){
        try{
            pstmt=ct.prepareStatement("select sum(Purchase_List_amount) from Purchase_List where date_sub(current_timestamp(), interval 1 month) <= Purchase_List_date;");
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public Integer income(){
        try{
            pstmt=ct.prepareStatement("select sum(Sale_List_amount) from Sale_List where date_sub(current_timestamp(), interval 1 month) <= Sale_List_date;");
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public Integer profit(){
        try{
            return income()-expand();
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public Integer purchaseamount(){
        try{
            pstmt=ct.prepareStatement("select count(Purchase_List_id) from Purchase_List where state=0;");
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public Integer saleamount(){
        try{
            pstmt=ct.prepareStatement("select count(Sale_List_id) from Sale_List where state=0;");
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public Integer warningamount(){
        try{
            pstmt=ct.prepareStatement("select count(Goods_id) from Good where Good_quantity<=lowerLimit;");
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public boolean allowpurchase(Byte permission){
        try{
            System.out.println(permission);
            pstmt=ct.prepareStatement("select Purchase from Permission where Permission_id=?;");
            pstmt.setByte(1,permission);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            if(rs.getByte(1)==1)return true;
            else return false;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean allowsale(Byte permission){
        try{
            System.out.println(permission);
            pstmt=ct.prepareStatement("select selling from Permission where Permission_id=?;");
            pstmt.setByte(1,permission);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            if(rs.getByte(1)==1)return true;
            else return false;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean allowinventory(Byte permission){
        try{
            System.out.println(permission);
            pstmt=ct.prepareStatement("select quantity from Permission where Permission_id=?;");
            pstmt.setByte(1,permission);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            if(rs.getByte(1)==1)return true;
            else return false;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean allowdata(Byte permission){
        try{
            System.out.println(permission);
            pstmt=ct.prepareStatement("select alters from Permission where Permission_id=?;");
            pstmt.setByte(1,permission);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            if(rs.getByte(1)==1)return true;
            else return false;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean allowmanage(Byte permission){
        try{
            System.out.println(permission);
            pstmt=ct.prepareStatement("select manage from Permission where Permission_id=?;");
            pstmt.setByte(1,permission);
            ResultSet rs=pstmt.executeQuery();
            rs.next();
            if(rs.getByte(1)==1)return true;
            else return false;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }




    public String getUserName(int id)  {
        String username=null;
        try {
            pstmt=ct.prepareStatement("select username from userTable where id=?");
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            while(rs.next())  {
                username=rs.getString(1);
            }
            return username;
        } catch(Exception e)  {
            e.printStackTrace();
            return null;
        }
    }


    public boolean insertUser(String username,String pwd){
        try{
            pstmt=ct.prepareStatement("insert into userTable (username, password) values(?,?)");
            pstmt.setString(1, username);
            pstmt.setString(2, pwd);
            pstmt.executeUpdate();
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

}